package com.lxw.oa.util;
import org.apache.poi.POIXMLDocument;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hwpf.HWPFDocument;
import org.apache.poi.hwpf.usermodel.Range;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xwpf.usermodel.*;
import java.io.*;
import java.text.SimpleDateFormat;
import java.util.*;

public class PoiExcelUtil {
    /**
     * 替换Excel模板文件内容
     * @param item 文档数据
     * @param sourceFilePath Excel模板文件路径
     *  targetFilePath Excel生成文件路径
     */
    public static Workbook replaceExcelModelHSS(Map<String,String>item, String sourceFilePath) {
        //boolean bool = true;
        HSSFWorkbook wb=null;
        try {
            POIFSFileSystem fs  =new POIFSFileSystem(new FileInputStream(sourceFilePath));
            wb = new HSSFWorkbook(fs);
            HSSFSheet sheet = wb.getSheetAt(0);
            Iterator rows = sheet.rowIterator();
            while(rows.hasNext()){
                HSSFRow row = (HSSFRow) rows.next();
                if(row!=null) {
                    int num = row.getLastCellNum();
                    for(int i=0;i<num;i++) {
                        HSSFCell cell=  row.getCell(i);
                        if(cell==null || cell.getStringCellValue()==null) {
                            continue;
                        }
                        String value= cell.getStringCellValue();
                        if(!"".equals(value)) {
                            Set<String> keySet = item.keySet();
                            Iterator<String> it = keySet.iterator();
                            while (it.hasNext()) {
                                String text = it.next();
                                if(value.equalsIgnoreCase(text)) {
                                    String textValue=item.get(text)+"";
                                    cell.setCellValue(textValue);
                                    break;
                                }
                            }
                        } else {
                            cell.setCellValue("");
                        }
                    }
                }
            }
            // 输出文件
/*            FileOutputStream fileOut = new FileOutputStream(targetFilePath);
            wb.write(fileOut);
            fileOut.close();*/

        } catch (Exception e) {
            //bool = false;
            e.printStackTrace();
        }
        return wb;
    }

    public static Workbook replaceExcelModelXSS(Map<String,String>item, String sourceFilePath) {
        XSSFWorkbook wb=null;
        try {
            //POIFSFileSystem fs  =new POIFSFileSystem(new FileInputStream(sourceFilePath));
            wb = new XSSFWorkbook(new FileInputStream(sourceFilePath));
            XSSFSheet sheet = wb.getSheetAt(0);
            Iterator rows = sheet.rowIterator();
            while(rows.hasNext()){
                XSSFRow row = (XSSFRow) rows.next();
                if(row!=null) {
                    int num = row.getLastCellNum();
                    for(int i=0;i<num;i++) {
                        XSSFCell cell=  row.getCell(i);
                        if(cell==null || cell.getStringCellValue()==null) {
                            continue;
                        }
                        String value= cell.getStringCellValue();
                        if(!"".equals(value)) {
                            Set<String> keySet = item.keySet();
                            Iterator<String> it = keySet.iterator();
                            while (it.hasNext()) {
                                String text = it.next();
                                if(value.equalsIgnoreCase(text)) {
                                    String textValue=item.get(text)+"";
                                    cell.setCellValue(textValue);
                                    break;
                                }
                            }
                        } else {
                            cell.setCellValue("");
                        }
                    }
                }
            }
            // 输出文件
/*            FileOutputStream fileOut = new FileOutputStream(targetFilePath);
            wb.write(fileOut);
            fileOut.close();*/

        } catch (Exception e) {
            //bool = false;
            e.printStackTrace();
        }
        return wb;
    }

    public static HSSFWorkbook exportExcelForAll(List<Map<String,Object>> data,String[] column,String[] columnCom){
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet("sheet0");
        HSSFRow rowTitle = sheet.createRow(0);
        for(int i=0;i<column.length;i++){
            HSSFCell cell = rowTitle.createCell(i);
            cell.setCellValue(columnCom[i]);
        }
        for (int i = 0; i < data.size(); i++) {
            Map t = data.get(i);
            HSSFRow row = sheet.createRow(i + 1);
            for(int j=0;j<column.length;j++){
                Object value = t.get(column[j]);
                String valueStr;
                if (value != null) {
                    valueStr = value.toString();
                } else {
                    valueStr = "";
                }
                if (value instanceof Date) {
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    valueStr = sdf.format((Date) value);
                }
                HSSFCell cell = row.createCell(j);
                cell.setCellValue(valueStr);
            }
        }
        return wb;
    }







   /* public static HSSFWorkbook exportExcel(List data, Map<String, String> fieldTitle)
            throws IllegalAccessException, NoSuchFieldException, IOException {
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet("sheet0");
        HSSFRow rowTitle = sheet.createRow(0);
        int i = 0;
        for (Map.Entry<String, String> entry : fieldTitle.entrySet()) {
            HSSFCell cell = rowTitle.createCell(i);
            cell.setCellValue(entry.getValue());
            i++;
        }
        for (i = 0; i < data.size(); i++) {
            Map t = (Map) data.get(i);
            HSSFRow row = sheet.createRow(i + 1);
            int j = 0;
            for (Map.Entry<String, String> entry : fieldTitle.entrySet()) {
                Object value = t.get(entry.getKey());
                String valueStr;
                if (value != null) {
                    valueStr = value.toString();
                } else {
                    valueStr = "";
                }
                if (value instanceof Date) {
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    valueStr = sdf.format((Date) value);
                }
                HSSFCell cell = row.createCell(j);
                cell.setCellValue(valueStr);
                j++;
            }
        }
        return wb;
    }*/

}

